Amazon Redshiftにおけるデータをマスクする方針について

#Amazon Redshift

#AWS

石川覚

2018.01.30

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

クラウドDWHであるAmazon Redshiftならではのデータをマスクする方針ついて解説します。Amazon RedshiftはDWHであり、DWHであるが故にあらゆるデータが統合されており、個人情報や取引先、売上情報の詳細に至るさまざまな機密情報の集合といえます。しかしビジネス要件とは異なる様々な理由でそのデータを持ち出さなければならないことが稀に起こります。

例えば：

クエリのパフォーマンスチューニングに利用するスナップショットを提供しなければならない
原因不明の障害が発生してクラスタのスナップショットをAWSサポートに提供しなければならない

機密情報は外部に一切持ち出せないというクライアント側と、再現性のない問題の調査・対策・その効果測定ができないという技術側と、両方の言い分は十分理解できます。この場合、技術側がどのようなアプローチでデータをマスクして、データの特性や再現性を失わずお客様が妥協できる範囲でデータを隠蔽できるか、誤解がないように説明できれば歩み寄る余地があると感じています。

データのマスクの方針

基本的にすべてを網羅できる複雑な変換ルールにしません。なぜなら、誰が担当するにせよ負担が大きいのは不本意なことであり、そもそも負担が大きいのであれば費用対効果の観点から実施できなくなるからです。

自動スナップショットから提供用クラスタを作成

本番環境のクラスタには一切変更や影響を与えないため、定期的に取得している自動スナップショットから別クラスタを作成して作業します。本番環境とは別のクラスタなので、本番環境の管理者のパスワードは上書きできるので知る必要がありません。

対象外の不要なテーブルは削除する

そもそも調査の対象外のテーブルのデータは持ち出す必要がありませんので可能な限り削除します。不要なテーブルのデータを削除することで、よりノード数の少ない環境で調査が可能になります。あくまでも、テーブルのレコード数を少なくするのではなく、テーブル単位で削除（TRUNCATE）します。

キー情報はマスクしない

キー情報をマスクしてしまうとテーブル間の結合が再現しない可能性があるのでマスクしません。技術的に調整の余地がありますが、ルールをシンプルにするため、割り切っています。

数値は乱数でマスクする

売上のような数値は乱数と係数を掛けてマスクします。乱数と乗算する目的は元の数値をわからなくするためです。係数と乗算する目的は値の行毎の平均値が0.5になりその値よりも大きくするためです。（が、かといってデータ型範囲を超えないような注意が必要です。）乱数は実行する度に異なる再現性のない数値が生成されます。

文字列のハッシュでマスクする

名前や住所、電話番号など個人を特定できる情報はハッシュでマスクします。文字列のハッシュは再現性がありカーディナリティやデータの偏り、データサイズは大きく変わることがないので本番環境のデータに近い状態を保つことができます。ハッシュ生成は不可逆変換なので、ハッシュから元の文字列を生成することは数学的に不可能です。

データのマスクの手順

方針に基づいて、データをマスクする手順は以下のとおりです。

１．Redshiftのスナップショットから提供用のクラスタを作成

本番環境の自動スナップショットから提供用クラスタを作成します。管理者パスワードは新しいものに上書きして、提供用クラスタにログインします。最新の自動スナップショットを選択して、[アクション]から[スナップショットからの復元]を選択するとダイアログが表示します。ダイアログに従いリストアしてください。

参考：ステップ 2: ターゲットクラスターにスナップショットを復元する

２．不要なテーブルのデータ削除

手作業で不要なテーブルのデータを削除するのは大変なので、データ削除用SQLを自動生成します。下記の例では、「除外するスキーマを列挙」していますが、用途に応じて条件をカスタマイズしてください。

SELECT
'TRUNCATE TABLE ' + QUOTE_IDENT(n.nspname) + '.' + QUOTE_IDENT(c.relname) + ';' AS truncate_target
FROM pg_namespace AS n
INNER JOIN pg_class AS c ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND n.nspname not in ('aaa', 'bbb', 'ccc') -- 除外するスキーマを列挙
ORDER BY n.nspname, c.relname
;
-- truncate_target
-- -------------------------------------------------------------------
-- TRUNCATE TABLE ddd.table_d1;
-- TRUNCATE TABLE ddd.table_d2;
-- TRUNCATE TABLE eee.table_e1;
-- :
-- :
-- TRUNCATE TABLE zzz.table_zn;

３．対象テーブルデータのマスク方法

下記のルールに基づいて、データにマスクするSQLの例です。

キー情報はマスクしない
売上等の数値の乱数でマスクする
文字列のSHR1のハッシュでマスクします
文字列は乱数は用いないことで、カーディナリティ、データの偏り、データ数、データサイズを維持します。

select
lo_ordertotalprice,
cast(random() * 1.5 * lo_ordertotalprice as int) as lo_ordertotalprice_mask,
lo_orderpriority,
substring(func_sha1(lo_orderpriority), 1, octet_length(lo_orderpriority)) as lo_orderpriority_mask
from lineorder
limit 10;
-- lo_ordertotalprice | lo_ordertotalprice_mask | lo_orderpriority | lo_orderpriority_mask
-- --------------------+-------------------------+------------------+-----------------------
-- 20000099 | 20355927 | 1-URGENT | 372cc49d
-- 0 | 0 | 4-NOT SPECI | d6cc201a9ea
-- 30360268 | 31751164 | 3-MEDIUM | a2e9c51b
-- 22586529 | 21232074 | 5-LOW | 52a3a
-- 0 | 0 | 4-NOT SPECI | d6cc201a9ea
-- 16275336 | 3522315 | 5-LOW | 52a3a
-- 24062265 | 3041954 | 3-MEDIUM | a2e9c51b
-- 0 | 0 | 4-NOT SPECI | d6cc201a9ea
-- 18453760 | 8918581 | 5-LOW | 52a3a
-- 28684728 | 18286139 | 2-HIGH | c4ded6
-- (10 rows)

テーブル内のデータを以下のように一律変換します。

-- begin;
update lineorder
set
lo_ordertotalprice = cast(random() * 1.5 * lo_ordertotalprice as int),
lo_orderpriority = substring(func_sha1(lo_orderpriority), 1, octet_length(lo_orderpriority));
-- commit;

4．データの特性や再現性を失われていないか確認する

クエリを実行して、マスク前後でデータの特性や再現性を失われていないかを確認します。動作が変わってしまったときはデータのマスク方法や係数を変え、再現できるように調整します。

5．スナップショット作成・共有

マスクしたデータを作成した後、スナップショットを作成して以下の手順に従い、スナップショットを共有します。

Amazon RedshiftのスナップショットをAWSアカウント間で共有する

データにマスクをするということはデータを更新している（未ソートのデータブロックが新たに作られている）ので、VACUUMが必要な状態です。しかし、VACUUMしていない状態でスナップショットを共有して、必要であれば共有先でVACUUMを実行します。それはできるだけ手を加えていない状態を共有先に提供するためです。

課題

今回ご紹介したマスクの方法はシンプルであるが故に、問題点も多くあります。現状の課題について列挙します。

データをマスクをするということはデータを更新しているので、データブロックの状態が変わっており、データの再現ができない可能性があります。
VACUUMを実行すると現象が再現しなくなってしまう可能性があります。
上記のFUNC_SHA1関数では40文字以上の文字列でも、40文字になってしまうという制限があり、データサイズが本番と異なる可能性があります。
上記のハッシュ文字列は、元の文字列長に合わせるため substring関数で長さを調整していますが、文字列が短い場合はハッシュキーが重複してしまうので、短い文字列（８バイト未満目安）はマスクに不向きです。

※ 「８バイト未満目安」の根拠は、Redshiftのソートキーは先頭の８バイトまでをキーにソートするためです。といいつつも、あくまでも目安です。

最後に

今回ご紹介した方法であれば、個人情報や取引先、売上情報などはほぼ完全に隠蔽しつつ、簡単にマスクできます。データをマスクしても、カーディナリティ、データの偏り、データサイズ、レコード長も概ね維持できるので、データの再現性を確保できるはずです。AWSサポートにスナップショットを提供したい、でもお客様が心配している場合などご参考になれば幸いです。